import utils
import os
import re
import json
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill, Border, Side, Alignment

fill_date = PatternFill(
    fill_type='solid', start_color='D9FDE900', end_color='D9FDE900')
fill_main = PatternFill(
    fill_type='solid', start_color='C6FDCC00', end_color='C6FDCC00')
fill_alt = PatternFill(
    fill_type='solid', start_color='BCD7E400', end_color='BCD7E400')

thin = Side(border_style="thin", color="000000")
border_normal = Border(top=thin, bottom=thin, left=thin, right=thin)
align_header = Alignment(horizontal='center')
align_header_v = Alignment(vertical='center', wrap_text=True)


# 拼接违规数据
def get_violation_info(item):
    title = item['Title']
    violation_content = re.sub('<.*?>', '', item['Content'])
    violation_cause = ''
    violation_title = ''
    violation_result = ''
    if re.search('违规处理', title):
        violation_cause = re.search('涉[嫌及](.+?)，', violation_content).group(1)
        violation_title = re.search(
            '违规(.*?)消息“(.*?)”', violation_content).group(2)
    elif re.search('功能屏蔽', title):
        if re.search('涉[嫌及](.+?)，', violation_content):
            violation_cause = re.search(
                '涉[嫌及](.+?)，', violation_content).group(1)
        elif re.search('您的帐号经查(.+?)，', violation_content):
            violation_cause = re.search(
                '您的帐号经查(.+?)，', violation_content).group(1)
        if re.search('违规内容：(.+?)如有异议', violation_content):
            violation_title = re.sub('&nbsp;', '', re.search(
                '违规内容：(.+?)如有异议', violation_content).group(1))
        elif re.search('流量主通过不正当方式制造虚假或无效曝光量、点击量', violation_content):
            violation_title = re.search(
                '流量主通过不正当方式制造虚假或无效曝光量、点击量', violation_content).group(0)
        features_by_date = {}
        features = re.findall('已屏蔽([^功能]+?)功能至([^,，]+)[，,]', violation_content)
        for feature in features:
            feature_name = feature[0]
            feature_date = feature[1]
            if feature_date in features_by_date:
                features_by_date[feature_date].append(feature_name)
            else:
                features_by_date[feature_date] = [feature_name]
        for feature_date, forbidden_features in features_by_date.items():
            violation_result += "{}: {}\n".format(
                feature_date, ','.join(forbidden_features))
    elif re.search('粉丝删除', title):
        violation_cause = re.search('所禁止的(.+?)，', violation_content).group(1)
        violation_title = re.sub('&nbsp;', '', re.search(
            '发起的"(.*?)"', violation_content).group(1))
        violation_result = re.search('已删除.+?粉丝数。', violation_content).group(0)
    elif re.search('责令处理', title):
        violation_title = re.search('图文消息“(.*?)”', violation_content).group(1)
        violation_result = re.search(
            '已被互联网信息内容主管部门责令删除', violation_content).group(0)
    return violation_cause, violation_title, violation_result


# 设置单元格宽度
def set_columns_width(sheet, columns_width):
    for index, width in enumerate(columns_width):
        sheet.column_dimensions[get_column_letter(index + 1)].width = width


# 设置单元格格式
def set_cell_format(sheet, column_letters, row_index, number_format):
    for column in column_letters:
        sheet[column + str(row_index)].number_format = number_format


def set_border(rows):
    for row in rows:
        for cell in row:
            cell.border = border_normal


def set_styles(rows, fill=None, alignment=None):
    for row in rows:
        for cell in row:
            if fill:
                cell.fill = fill
            if alignment:
                cell.alignment = alignment


# 拼接基本信息
def get_basic_data(b):
    return [b.get('charge'), b.get('nickname'), b.get('wx_id'), b.get('gh_id'),
            b.get('fans_src'), b.get('type'), b.get('gender')]


# 拼接公司主体信息
def get_basic_data_gs(b):
    return [b.get('gs')]


def get_app_msg_stat(m, row):
    all_uv = all_pv = dialog_uv = dialog_pv = moment_uv = moment_pv = share_user = share_cnt = fav_user = fav_cnt = 0
    hist = 0  # TODO
    for item in m.get('item', []):
        fav_user += item['add_to_fav_user']
        fav_cnt += item['add_to_fav_count']
        src = item['user_source']
        if src == 99999999:
            all_pv = item['int_page_read_count']
            all_uv = item['int_page_read_user']
        elif src == 0:
            dialog_pv = item['int_page_read_count']
            dialog_uv = item['int_page_read_user']
        elif src == 2:
            moment_pv = item['int_page_read_count']
            moment_uv = item['int_page_read_user']
    for share in m.get('share', []):
        share_user += share['share_user']
        share_cnt += share['share_count']
    return [all_uv, all_pv, dialog_uv, dialog_pv, moment_uv, moment_pv, share_user, share_cnt, fav_user, fav_cnt, hist,
            '=Q{0}+S{0}+U{0}+X{0}'.format(row),  # 真实阅读
            '=M{0}/I{0}'.format(row),  # 头条阅读率
            '=O{0}/I{0}'.format(row),  # 综合阅读率
            '=Y{0}/I{0}'.format(row),  # 真实阅读率
            '=(S{0}+U{0})/(Q{0}+X{0})'.format(row),  # 二次传播比
            '=(Q{0}+X{0})/(P{0}+X{0})'.format(row),  # 人均打开次数
            '=U{0}/Y{0}'.format(row),  # 转发率
            '=P{0}/I{0}'.format(row),  # 打开率
            ]


def get_app_msg_detail_stat(messages, date_to_find):
    sent_messages = []  # 服务号可能一天发送多条消息
    for message in messages:
        # 图文类型才有阅读数统计
        if message['type'] == 9 and utils.get_date_from_timestamp(message['sent_info']['time']) == date_to_find:
            sent_message = [utils.get_datetime_from_timestamp(
                message['sent_info']['time'])]
            for message_item in message['appmsg_info']:
                sent_message.append(message_item['title'])
                sent_message.append(message_item['other_info']['read_num'])
            sent_messages.append(sent_message)
    return sent_messages if sent_messages != [] else [['']]


def get_publisher_stat(p, row):
    if p == "EMPTY":
        return ['-', '-', '-', '-', '-', '-', '-', '-', '-']
    return [p.get('view_count', 0), p.get('click_count', 0), p.get('cost', 0) / 100.0,
            '=AG{0}/O{0}'.format(row),  # 曝光率
            '=AH{0}/AG{0}'.format(row),  # 点击率
            '=AI{0}/AG{0}'.format(row),  # 曝光单价
            '=AI{0}/AH{0}'.format(row),  # 点击单价
            '=AI{0}/O{0}'.format(row),  # 阅读单价
            '=AI{0}/I{0}'.format(row),  # 粉丝单价
            ]


def get_all_publisher(gh_id, date_list):
    res = []
    for item in date_list:
        data = utils.get_json_file(gh_id, item, 'publisher_by_day.json')
        if data:
            if isinstance(data, str):
                res.append(data)
            else:
                res.append(data.get('income') / 100.0)
        else:
            res.append('0')
    return res


# 获取时间段里流量主总收入
def get_summary_publisher(p):
    return [p.get('summary').get('income') / 100.0]


def get_publisher_by_day(p):
    data = []
    _list = p.get('list')
    for item in _list:
        data.append('{0}日收入{1}元'.format(
            item.get('date'), item.get('income') / 100.0))
    return data


# 头条阅读数
def get_app_msg_topmost_read(i, ma, date):
    if ma.get('total') == []:
        return '当天未发'

    msg = ''

    if len(i):
        for m in i:
            if m['type'] == 9 and utils.get_date_from_timestamp(m['sent_info']['time']) == date:
                info = m['appmsg_info']
                other = info[0].get('other_info')
                if 'read_num' in other:
                    msg = other['read_num']
                    break
            msg = '非图文类型'
    else:
        msg = '当天未发'

    return msg


# 拼接用户男女数据
def get_user_gender_ratio(genders, total, needed):
    if not needed or not total:
        return ''
    needed = needed.strip()
    if not genders:
        return ''
    try:
        for gender in genders:
            if gender['attr_name'] == needed:
                return gender['user_count'] / total
        return ''
    except (KeyError, ValueError):
        print(genders)
        return ''


def get_ad_stat(ad):
    if ad == '未开通广告主':
        return [ad]
    elif ad == '广告主开通但未绑定服务商':
        return [ad]
    else:
        return [
            ad.get('agencies')[0].get('agency_name'),
            format(ad.get('agencies')[0].get(
                'balance').get('CASH')/float(100), '0.2f')
        ]


def get_detail_invoice_info(invoice):
    order_status_str = ''
    invoice_type_str = ''
    makeout_status_str = ''
    download_str = ''
    if len(invoice):
        for order in invoice:
            create_time = utils.unix_to_date(order.get('create_time'))
            if 'order_status' in order:
                order_status = utils.get_order_status(order.get('order_status'))
                order_status_str = order_status_str + create_time + ':' + order_status + '\n'
            if 'invoice_type' in order:
                invoice_type = utils.get_invoice_type(order.get('invoice_type'))
                invoice_type_str = invoice_type_str + create_time + ':' + invoice_type + '\n'
            if 'makeout_status' in order.get('invoice'):
                makeout_status = utils.get_makeout_status(order.get('invoice').get('makeout_status'))
                makeout_status_str = makeout_status_str + create_time + ':' + makeout_status + '\n'
            if 'download' in order:
                download = order.get('download')
                download_str = download_str + create_time + ':' + download + '\n'
    return [order_status_str, invoice_type_str, makeout_status_str, download_str]


def get_detail_transfer_invoice_info(invoice):
    if len(invoice):
        return invoice
    return ['', '', '']


# 生成表格
class ReportGenerator:
    def __init__(self, library, date_to_report):
        """
        :param utils.AccountLibrary library:
        :param date_to_report:
        """
        self.date = date_to_report
        self.library = library
        self.gh_id = None

    # 读取基础信息
    def get_basic(self, gh_id, today = False):
        date = self.date
        if self.date == utils.get_today():
            date = utils.get_yesterday()
        if today:
            date = utils.get_today()
        return utils.get_json_file(gh_id, date, 'basic.json')

    # 读取用户数据
    def get_user_counter(self, gh_id):
        date = self.date
        if self.date == utils.get_today():
            date = utils.get_yesterday()
        return utils.get_json_file(gh_id, date, 'user-counter.json')

    # 读取广告主数据
    def get_ad_info(self, gh_id):
        return utils.get_json_file(gh_id, utils.get_today(), 'app-ad-info.json')

    def get_user_attr(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'user-attr.json')

    def get_msg_counter(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'app-msg-counter.json')

    def get_msg_all(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'app-msg-all.json')

    def get_publisher_counter(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'publisher.json')

    # 读取违规数据
    def get_violation_list(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'violation-list.json', data_type=list)

    def get_msg_info(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'app-msg-info.json', data_type=list)

    # 读取迁移数据
    def get_transfer_info(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'transfer.json', data_type=str)

    # 读取认证发票数据
    def get_invoice_info(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'invoice_info.json', data_type=dict)

    # 读取迁移发票数据
    def get_transfer_invoice_info(self, gh_id):
        return utils.get_json_file(gh_id, self.date, 'transfer_invoice.json', data_type=list)

    def log_line(self, msg, gh_id=None):
        gh_nickname = self.library.get_account_data(gh_id).get('nickname')
        line = '[{}]({}) {}'.format(
            gh_nickname, gh_id if gh_id else self.gh_id, msg)
        print(line)
        return line

    def log_err(self, err, logtype='log', gh_id=None):
        utils.log_error(self.log_line(err, gh_id), logtype)

    def save_excel(self, workbook, name):
        filename = 'output/{} {}.xlsx'.format(self.date, name)
        try:
            workbook.save(filename)
            workbook.close()
        except PermissionError:
            print('无法写入{}，请检查是否在Excel中打开了文件，如有需要请另存为并关闭'.format(filename))

    def make_daily_data(self):
        workbook, sheet = utils.make_workbook()
        set_columns_width(sheet, [12, 20, 8, 15, 10, 6, 5,
                                  6.5, 8, 6, 6, 6,
                                  9, 9, 9, 9, 9, 9,
                                  9, 9, 9, 9, 9, 9,
                                  8, 9, 9, 9, 9, 9, 6, 6,
                                  6, 6, 7, 6, 6, 8, 8, 8, 8, 20])
        sheet.append(['日期', self.date])
        sheet.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '男/女',
                      '男女率', '累计关注', '新增', '取关', '净增',
                      '头条阅读数', '综合阅读人数', '综合阅读次数', '会话阅读人数', '会话阅读次数', '朋友圈阅读人数',
                      '朋友圈阅读次数', '转发人数', '转发次数', '收藏人数', '收藏次数', '历史消息阅读次数',
                      '真实阅读', '头条阅读率', '综合阅读率', '真实阅读率', '二次传播比', '人均打开次数', '转发率', '打开率',
                      '曝光量', '点击量', '总收入', '曝光率', '点击率', '曝光单价', '点击单价', '阅读单价', '粉丝单价', '主体公司'])
        row_index = 2
        for gh_id in self.library.get_account_list():
            row_index += 1
            b = self.get_basic(gh_id)
            ua = self.get_user_attr(gh_id)
            u = self.get_user_counter(gh_id)
            m = self.get_msg_counter(gh_id)
            ma = self.get_msg_all(gh_id)
            i = self.get_msg_info(gh_id)
            p = self.get_publisher_counter(gh_id)
            total_user = u.get('cumulate_user')
            sheet.append(get_basic_data(b) + [
                get_user_gender_ratio(
                    ua.get('genders'), total_user, b.get('gender')),
                total_user, u.get('new_user'), u.get(
                    'cancel_user'), u.get('netgain_user'),
                get_app_msg_topmost_read(i, ma, self.date)] + get_app_msg_stat(m, row_index) + get_publisher_stat(p, row_index) + get_basic_data_gs(b))
            set_cell_format(
                sheet, ['H', 'Z', 'AA', 'AB', 'AC', 'AD', 'AJ', 'AK'], row_index, '0.0%')
            set_cell_format(
                sheet, ['AC', 'AD', 'AL', 'AM', 'AN', 'AO'], row_index, '0.0000')
        sheet['A1'].fill = fill_date
        set_styles(sheet['A2:X2'], fill_main, align_header)
        set_styles(sheet['Y2:AF2'], fill_alt, align_header)
        set_styles(sheet['AG2:AI2'], fill_main, align_header)
        set_styles(sheet['AJ2:AP2'], fill_alt, align_header)
        set_border(sheet['A2:AP{}'.format(row_index)])
        sheet.freeze_panes = 'H3'
        self.save_excel(workbook, '数据表格')

    def make_app_msg_compare(self, diff):
        workbook, sheet = utils.make_workbook()
        sheet.append(['日期', self.date])
        header_width = [20, 12, 20, 8, 15, 10, 6, 5, 8, 20]
        headers = ['主体公司', '负责人', '名称', '微信号', '原始ID',
                   '粉丝来源', '类型', '男/女', '累计关注', '图文发送时间']
        for index in range(1, 9):
            headers += ['{}条标题'.format(index), '{}条阅读'.format(index)]
            header_width += [7, 7]
        set_columns_width(sheet, header_width)
        sheet.append(headers)
        row_index = 2
        for gh_id in self.library.get_account_list():
            b = self.get_basic(gh_id)
            u = self.get_user_counter(gh_id)
            i = self.get_msg_info(gh_id)
            for msg in get_app_msg_detail_stat(i, utils.get_day_before_today(diff, self.date)):
                row_index += 1
                sheet.append(get_basic_data_gs(
                    b) + get_basic_data(b) + [u.get('cumulate_user')] + msg)
        sheet['A1'].fill = fill_date
        set_styles(sheet['A2:H2'], fill_main, align_header)
        set_styles(sheet['I2:Z2'], fill_alt, align_header)
        set_border(sheet['A2:Z{}'.format(row_index)])
        sheet.freeze_panes = 'I3'
        self.save_excel(workbook, '{}小时阅读对比'.format(12 + diff * 24))

    # 导入违规数据
    def make_violation_data(self):
        workbook, sheet = utils.make_workbook()
        set_columns_width(
            sheet, [8, 20, 10, 20, 5, 8, 5, 12, 30, 30, 40, 8, 20])
        sheet.append(['日期', self.date])
        sheet.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '男/女', '累计关注',
                      '违规类型', '违规内容', '违规处理', '是否封禁', '主体公司'])
        row_index = 2
        for gh_id in self.library.get_account_list():
            b = self.get_basic(gh_id)
            u = self.get_user_counter(gh_id)
            v = self.get_violation_list(gh_id)
            if v:
                try:
                    violationlist_by_title = {}
                    for value in v:
                        violation_cause, violation_title, violation_result = get_violation_info(
                            value)
                        if violation_title in violationlist_by_title:
                            violationlist_by_title[violation_title] = [
                                violation_cause, violationlist_by_title[violation_title][1] + violation_result]
                        else:
                            violationlist_by_title[violation_title] = [
                                violation_cause, violation_result]

                    for vio_title, vio_info in violationlist_by_title.items():
                        row_index += 1
                        sheet.append(get_basic_data(b) +
                                     [u.get('cumulate_user'), vio_info[0], vio_title, vio_info[1], '是' if vio_info[1] else '否'] + get_basic_data_gs(b))
                except:
                    print(gh_id)
        sheet['A1'].fill = fill_date
        set_styles(sheet['A2:M2'], fill_main, align_header)
        set_styles(sheet['A2:M{}'.format(row_index)], alignment=align_header_v)
        set_border(sheet['A2:M{}'.format(row_index)])
        sheet.freeze_panes = 'I3'
        self.save_excel(workbook, '违规数据')

    # 导入广告主数据
    def make_advertiser_data(self):
        workbook, sheet = utils.make_workbook()
        set_columns_width(sheet, [8, 20, 10, 20, 5, 8, 5, 12, 30, 15, 20])
        sheet.append(['日期', utils.get_today()])
        sheet.append(['负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '男/女', '累计关注',
                      '服务商', '金额', '主体公司'])
        row_index = 2
        for gh_id in self.library.get_account_list():
            row_index += 1
            b = self.get_basic(gh_id)
            u = self.get_user_counter(gh_id)
            ad = self.get_ad_info(gh_id)
            if ad:
                sheet.append(get_basic_data(
                    b) + [u.get('cumulate_user')] + get_ad_stat(ad) + get_basic_data_gs(b))
            else:
                sheet.append(get_basic_data(
                    b) + [u.get('cumulate_user'), '-', '-'] + get_basic_data_gs(b))
        sheet['A1'].fill = fill_date
        set_styles(sheet['A2:K2'], fill_main, align_header)
        set_border(sheet['A2:K{}'.format(row_index)])
        self.save_excel(workbook, '广告主')

    # 导入一段时间类的流量主数据
    def make_publisher_data(self, sdate, edate):
        date_list = utils.get_day_interval_format(sdate, edate)
        workbook, sheet = utils.make_workbook()
        header_width = [30, 12, 20, 15, 20, 10, 8, 8]
        headers = ['主体公司', '负责人', '名称', '微信号', '原始ID', '粉丝来源', '类型', '男/女']
        for item in date_list:
            headers += ['{}'.format(item)]
            header_width += [15]
        set_columns_width(sheet, header_width)
        sheet.append(headers)
        row_index = 2
        for gh_id in self.library.get_account_list():
            row_index += 1
            b = self.get_basic(gh_id)
            sheet.append(get_basic_data_gs(b) + get_basic_data(b) +
                         get_all_publisher(gh_id, date_list))
        set_styles(sheet['A1:H1'], fill_main, align_header)
        set_styles(sheet['I1:Z1'], fill_alt, align_header)
        set_border(sheet['A1:Z{}'.format(row_index)])
        # sheet.freeze_panes = 'I1'
        self.save_excel(workbook, '流量主表格')

    # 导入迁移状态数据
    def make_transfer_data(self, date):
        workbook, sheet = utils.make_workbook()
        set_columns_width(sheet, [20, 20, 15])
        sheet.append(['名称', '原始ID', '迁移状态'])
        row_index = 2
        for gh_id in self.library.get_account_list():
            row_index += 1
            b = self.get_basic(gh_id)
            t = self.get_transfer_info(gh_id)
            sheet.append([b.get('nickname'), b.get('gh_id'), t])
        self.save_excel(workbook, '迁移状态表格')

    # 导入认证发票数据
    def make_invoice_data(self):
        workbook, sheet = utils.make_workbook()
        set_columns_width(sheet, [20, 20, 25, 25, 25, 25])
        sheet.append(['名称', '原始ID', ' 审核状态', '发票类型', '开票状态', '是否下载'])
        row_index = 2
        for gh_id in self.library.get_account_list():
            row_index += 1
            b = self.get_basic(gh_id, True)
            i = self.get_invoice_info(gh_id)
            sheet.append([b.get('nickname'), b.get('gh_id')] + get_detail_invoice_info(i))
        set_styles(sheet['A2:F{}'.format(row_index)], alignment=align_header_v)
        self.save_excel(workbook, '认证发票信息表格')

    # 导入迁移发票数据
    def make_transfer_invoice_data(self):
        workbook, sheet = utils.make_workbook()
        set_columns_width(sheet, [20, 20, 15, 15, 15, 15])
        sheet.append(['原始ID', ' 迁移状态', '发票类型', '开票状态'])
        row_index = 2
        for gh_id in self.library.get_account_list():
            row_index += 1
            i = self.get_transfer_invoice_info(gh_id)
            sheet.append([gh_id] + get_detail_transfer_invoice_info(i))
        self.save_excel(workbook, '迁移发票信息表格')   
        

    # 生成主要数据表格
    def make_all(self):
        os.makedirs('output', exist_ok=True)
        self.make_daily_data()
        self.make_violation_data()
        self.make_app_msg_compare(0)
        self.make_app_msg_compare(1)

    # 生成今天广告主数据表格
    def make_today_ad(self):
        os.makedirs('output', exist_ok=True)
        self.make_advertiser_data()

    # 生成今天的违规数据表格
    def make_today_violation(self):
        os.makedirs('output', exist_ok=True)
        self.make_violation_data()

    # 生成某个时间段流量主数据表格
    def make_all_publisher(self, start_date, end_date):
        _start = int(start_date[-2:])
        _end = int(end_date[-2:])
        os.makedirs('output', exist_ok=True)
        self.make_publisher_data(start_date, end_date)

    # 生成迁移状态表格
    def make_transfer(self, date):
        os.makedirs('output', exist_ok=True)
        self.make_transfer_data(date)

    # 生成认证发票信息表格
    def make_invoice(self):
        os.makedirs('output', exist_ok=True)
        self.make_invoice_data()

    # 生成迁移发票信息表格
    def make_transfer_invoice(self):
        os.makedirs('output', exist_ok=True)
        self.make_transfer_invoice_data()
    

if __name__ == '__main__':
    yesterday = utils.get_yesterday()
    date = input('请输入报告日期，默认为昨天({}): '.format(yesterday))
    reporter = ReportGenerator(
        utils.default_library(), date if date else yesterday)
    reporter.make_all()
    print('生成完毕')
